#!/bin/bash 
BACKUP_DATE=`date +%F\ %T` 
WEEK=`date +%w` 
IP=`/sbin/ifconfig eth0|/bin/grep "inet addr"|/bin/cut -d : -f 2|/bin/awk '{print $1}'` 
BACKUP_PATH="/backup/${IP}/mysql"
LOG_FILE="${BACKUP_PATH}/mysql_${BACKUP_DATE}.log"
MYSQL_USER='root'
MYSQL_PASSWORD='baichi'
MYSQL_PATH="/usr/local/mysql/bin"
MYSQL="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD}"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASSWORD}"
# if BACKUP_PATH doesn't exists,mkdir 
[ ! -d "${BACKUP_PATH}" ] && mkdir -p "${BACKUP_PATH}"
#========================Backup start========================== 
echo "--------------backup start ${BACKUP_DATE}-----------------">>${LOG_FILE} 
# stop slave SQL_THREAD and flush logs and read from master status to know binlog file name and positon 
${MYSQL} -e "stop slave SQL_THREAD;flush logs;"
${MYSQL} -e "show master status \G">>${LOG_FILE} 
echo >>${LOG_FILE} 
#start backup every table 
DBNAME="`${MYSQL} -e 'show databases;'|sed '1d'`"
echo "DATABASE LIST:" >>${LOG_FILE} 
echo ${DBNAME}>>${LOG_FILE} 
echo >>${LOG_FILE} 
for dbname in ${DBNAME} 
do
    mkdir -p "${BACKUP_PATH}/${dbname}_${WEEK}"
    #cd /backup/${dbname}_${WEEK} 
    echo -e "${dbname} backup start ${BACKUP_DATE}">>"${LOG_FILE}"
    TABNAME=`${MYSQL} ${dbname} -e 'show tables;'|sed '1d'` 
        echo "TABLE LIST:" >>${LOG_FILE} 
        echo ${TABNAME}>>${LOG_FILE} 
        echo >>${LOG_FILE} 
    for tabname in ${TABNAME} 
    do
        ${MYSQL_DUMP} ${dbname} ${tabname}>${BACKUP_PATH}/${dbname}_${WEEK}/${tabname}.sql 
    done
done
${MYSQL} -e "start slave SQL_THREAD;flush logs;"
echo "--------------backup end ${BACKUP_DATE}-----------------">>${LOG_FILE} 
mail -s "<a href="http://www.it165.net/database/dbmy/" target="_blank" class="keylink">MySQL</a> fenbiao backup log" root@localhost<"${LOG_FILE}"




